Skip Headers

Oracle® Database JPublisher User's Guide 10g Release 2 (10.2) Part Number B14188-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Data Type and Java-to-Java Type Mappings

This chapter discusses the JPublisher support for data type mapping, including a section on JPublisher styles and style files for Java-to-Java type mappings. These style files are primarily used to provide Web services support. The chapter contains the following sections:

- JPublisher Data Type Mappings

- Support for PL/SQL Data Types

- JPublisher Styles and Style Files

## 3.1 JPublisher Data Type Mappings

This section covers the JPublisher functionality for mapping from SQL and PL/SQL to Java in the following topics:

- Overview of JPublisher Data Type Mappings

- SQL and PL/SQL Mappings to Oracle and JDBC Types

- JPublisher User Type Map and Default Type Map

- JPublisher Logical Progression for Data Type Mappings

- Object Attribute Types

- REF CURSOR Types and Result Sets Mapping

- Connection in JDBC Mapping

See Also: "Support for PL/SQL Data Types"

### 3.1.1 Overview of JPublisher Data Type Mappings

When you use the `-builtintypes`, `-lobtypes`, `-numbertypes`, and `-usertypes` type mapping options, you can specify one of the following settings for data type mappings:

- `oracle`

- `jdbc`

- `objectjdbc`

- `bigdecimal`

Note: The `objectjdbc` and `bigdecimal` settings are for the `-numbertypes` options only.

These mappings affect the argument and result types that JPublisher uses in the methods it generates.

The class that JPublisher generates for an object type has the `get` `XXX``()` and `set` `XXX``()` accessor methods for the object attributes. The class that JPublisher generates for a `VARRAY` or nested table type has the `get` `XXX``()` and `set` `XXX``()` methods, which access the elements of the array or nested table. When generation of wrapper methods is enabled, the class that JPublisher generates for an object type or PL/SQL package has wrapper methods. These wrapper methods invoke server methods, or stored procedures, of the object type or package. The mapping options control the argument and result types that these methods use.

The Java Database Connectivity (JDBC) and Object JDBC mappings use familiar Java types that can be manipulated using standard Java operations. The Oracle mapping is the most efficient mapping. The `oracle.sql` types match the Oracle internal data types as closely as possible so that little or no data conversion is required between the Java and SQL formats. You do not lose any information and have greater flexibility in how you process and unpack the data. If you are manipulating data or moving data within the database, then the Oracle mappings for standard SQL types are the most convenient representations. For example, performing `SELECT` and `INSERT` operations from one existing table to another. When data format conversion is necessary, you can use methods in the `oracle.sql.*` classes to convert to Java native types.

### 3.1.2 SQL and PL/SQL Mappings to Oracle and JDBC Types

Table 3-1 lists the mappings from SQL and PL/SQL data types to Java types. You can use all the supported data types listed in this table as argument or result types for PL/SQL methods. You can also use a subset of the data types as object attribute types.

See Also: "Object Attribute Types"

The SQL and PL/SQL Data Type column contains all possible data types.

The Oracle Mapping column lists the corresponding Java types that JPublisher uses when all the type mapping options are set to `oracle`. These types are found in the `oracle.sql` package provided by Oracle and are designed to minimize the overhead incurred when converting Oracle data types to Java types.

See Also: Oracle Database JDBC Developer's Guide and Reference for more information on the `oracle.sql` package

The JDBC Mapping column lists the corresponding Java types that JPublisher uses when all the type mapping options are set to `jdbc`. For standard SQL data types, JPublisher uses Java types specified in the JDBC specification. For SQL data types that are Oracle extensions, JPublisher uses the `oracle.sql.*` types. When you set the `-numbertypes` option to `objectjdbc`, the corresponding types are the same as in the JDBC Mapping column, except that primitive Java types, such as `int`, are replaced with their object counterparts, such as `java.lang.Integer`.

Note: Type correspondences explicitly defined in the JPublisher type map, such as PL/SQL `BOOLEAN` to SQL `NUMBER` to Java `boolean`, are not affected by the mapping option settings.

A few data types are not directly supported by JPublisher, in particular those types that pertain only to PL/SQL. You can overcome these limitations by providing equivalent SQL and Java types, as well as PL/SQL conversion functions between PL/SQL and SQL representations. The annotations and subsequent sections explain these conversions further.

Table 3-1 SQL and PL/SQL Data Type to Oracle and JDBC Mapping Classes

SQL and PL/SQL Data Type | Oracle Mapping | JDBC Mapping
--- | --- | ---
`CHAR`, `CHARACTER`, `LONG`, `STRING`, `VARCHAR`, `VARCHAR2` | `oracle.sql.CHAR` | `java.lang.String`
`NCHAR`, `NVARCHAR2` | `oracle.sql.NCHAR` (note 1) | `oracle.sql.NString` (note 1)
`NCLOB` | `oracle.sql.NCLOB` (note 1) | `oracle.sql.NCLOB` (note 1)
`RAW`, `LONG RAW` | `oracle.sql.RAW` | `byte[]`
`BINARY_INTEGER`, `NATURAL`, `NATURALN`, `PLS_INTEGER`, `POSITIVE`, `POSITIVEN`, `SIGNTYPE`, `INT`, `INTEGER` | `oracle.sql.NUMBER` | `int`
`DEC`, `DECIMAL`, `NUMBER`, `NUMERIC` | `oracle.sql.NUMBER` | `java.math.BigDecimal`
`DOUBLE PRECISION`, `FLOAT` | `oracle.sql.NUMBER` | `double`
`SMALLINT` | `oracle.sql.NUMBER` | `int`
`REAL` | `oracle.sql.NUMBER` | `float`
`DATE` | `oracle.sql.DATE` | `java.sql.Timestamp`
`TIMESTAMP` `TIMESTAMP WITH TZ` `TIMESTAMP WITH LOCAL TZ` | `oracle.sql.TIMESTAMP` `oracle.sql.TIMESTAMPTZ` `oracle.sql.TIMESTAMPLTZ` | `java.sql.Timestamp`
`INTERVAL YEAR TO MONTH` `INTERVAL DAY TO SECOND` | `String` (note 2) | `String` (note 2)
`ROWID`, `UROWID` | `oracle.sql.ROWID` | `oracle.sql.ROWID`
`BOOLEAN` | `boolean` (note 3) | `boolean` (note 3)
`CLOB` | `oracle.sql.CLOB` | `java.sql.Clob`
`BLOB` | `oracle.sql.BLOB` | `java.sql.Blob`
`BFILE` | `oracle.sql.BFILE` | `oracle.sql.BFILE`
Object types | Generated class | Generated class
SQLJ object types | Java class defined at type creation | Java class defined at type creation
`OPAQUE` types | Generated or predefined class (note 4) | Generated or predefined class (note 4)
`RECORD` types | Through mapping to SQL object type (note 5) | Through mapping to SQL object type (note 5)
Nested table, `VARRAY` | Generated class implemented using `oracle.sql.ARRAY` | `java.sql.Array`
Reference to object type | Generated class implemented using `oracle.sql.REF` | `java.sql.Ref`
`REF CURSOR` | `java.sql.ResultSet` | `java.sql.ResultSet`
Index-by tables | Through mapping to SQL collection (note 6) | Through mapping to SQL collection (note 6)
Scalar (numeric or character) Index-by tables | Through mapping to Java array (note 7) | Through mapping to Java array (note 7)
User-defined subtypes | Same as for base type | Same as for base type

Data Type Mapping Notes The following notes correspond to the entries in the preceding table:

1. The Java classes `oracle.sql.NCHAR`, `oracle.sql.NCLOB`, and `oracle.sql.NString` are not part of JDBC but are distributed with the JPublisher run time. JPublisher uses these classes to represent the `NCHAR` form of use of the corresponding classes, `oracle.sql.CHAR`, `oracle.sql.CLOB`, and `java.lang.String`.

2. Mappings of SQL `INTERVAL` types to the Java `String` type are defined in the JPublisher default type map. Functions from the `SYS.SQLJUTL` package are used for the conversions.

   See Also: "JPublisher User Type Map and Default Type Map"

3. Mapping of PL/SQL `BOOLEAN` to SQL `NUMBER` and Java `boolean` is defined in the default JPublisher type map. This process uses conversion functions from the `SYS.SQLJUTL` package.

4. Mapping of the `SYS.XMLTYPE` SQL `OPAQUE` type to the `oracle.xdb.XMLType` Java class is defined in the default JPublisher type map. For other `OPAQUE` types, the vendor typically provides a corresponding Java class. In this case, you must specify a JPublisher type map entry that defines the correspondence between the SQL `OPAQUE` type and the corresponding Java wrapper class. If JPublisher encounters an `OPAQUE` type that does not have a type map entry, then it generates a Java wrapper class for that `OPAQUE` type.

   See Also: "Type Mapping Support for OPAQUE Types"

5. To support a PL/SQL `RECORD` type, JPublisher maps the `RECORD` type to a SQL object type and then to a Java type corresponding to the SQL object type. JPublisher generates two SQL scripts. One script is to create the SQL object type and to create a PL/SQL package containing the conversion functions between the SQL type and the `RECORD` type. The other script is used to drop the SQL type and the PL/SQL package created by the first script.

   See Also: "Type Mapping Support for PL/SQL RECORD and Index-By Table Types"

6. To support a PL/SQL index-by table type, JPublisher first maps the index-by table type into a SQL collection type and then maps it into a Java class corresponding to that SQL collection type. JPublisher generates two SQL scripts. One to create the SQL collection type and to create a PL/SQL package containing conversion functions between the SQL collection type and the index-by table type. The other to drop the collection type and the PL/SQL package created by the first script.

   See Also: "Type Mapping Support for PL/SQL RECORD and Index-By Table Types"

7. If you use the JDBC driver to call PL/SQL stored procedures or object methods, then you have direct support for scalar index-by tables, also known as PL/SQL `TABLE` types. In this case, you need to use a type map entry for JPublisher that specifies the PL/SQL scalar index-by table type and a corresponding Java array type. JPublisher can then automatically publish PL/SQL or object method signatures that use this scalar index-by type.

   See Also: "Type Mapping Support for Scalar Index-by Tables"

### 3.1.3 JPublisher User Ty pe Map and Default Type Map

JPublisher has a user type map, which is controlled by the `-typemap` and `-addtypemap` options and starts out empty. It also has a default type map, which is controlled by the `-defaulttypemap` and `-adddefaulttypemap` options and starts with entries such as the following:

    jpub.defaulttypemap=SYS.XMLTYPE:oracle.xdb.XMLType
    jpub.adddefaulttypemap=BOOLEAN:boolean:INTEGER:
    SYS.SQLJUTL.INT2BOOL:SYS.SQLJUTL.BOOL2INT
    jpub.adddefaulttypemap=INTERVAL DAY TO SECOND:String:CHAR:
    SYS.SQLJUTL.CHAR2IDS:SYS.SQLJUTL.IDS2CHAR
    jpub.adddefaulttypemap=INTERVAL YEAR TO MONTH:String:CHAR:
    SYS.SQLJUTL.CHAR2IYM:SYS.SQLJUTL.IYM2CHAR

These commands, which include some wraparound lines, indicate mappings between PL/SQL types, Java types, and SQL types. Where applicable, they also specify conversion functions to convert between PL/SQL types and SQL types.

See Also: "Type Map Options"

JPublisher checks the default type map first. If you attempt in the user type map to redefine a mapping that is in the default type map, JPublisher generates a warning message and ignores the redefinition. Similarly, attempts to add mappings through `-adddefaulttypemap` or `-addtypemap` settings that conflict with previous mappings are ignored and generate warnings.

There are typically two scenarios for using the type maps:

- Specify type mappings for PL/SQL data types that are unsupported by JDBC.

- Avoid regenerating a Java class to map to a user-defined type. For example, assume you have a user-defined SQL object type, `STUDENT`, and have already generated a `Student` class to map to it. If you specify the `STUDENT:Student` mapping in the user type map, then JPublisher finds the `Student` class and uses it for mapping without regenerating it.

  See Also: "Example: Using the Type Map to Avoid Regeneration"

To use custom mappings, it is recommended that you clear the default type map, as follows:

    -defaulttypemap=

Then use the `-addtypemap` option to put any required mappings into the user type map.

The predefined default type map defines a correspondence between the `SYS.XMLTYPE` SQL `OPAQUE` type and the `oracle.xdb.XMLType` Java wrapper class. In addition, it maps the PL/SQL `BOOLEAN` type to the Java `boolean` type and the SQL `INTEGER` type through two conversion functions defined in the `SYS.SQLJUTL` package. Also, the default type map provides mappings between the SQL `INTERVAL` type and the Java `String` type.

However, you may prefer mapping the PL/SQL `BOOLEAN` type to the Java object type `Boolean` to capture the SQL `NULL` values in addition to the `true` and `false` values. You can accomplish this by resetting the default type map, as shown by the following:

    -defaulttypemap=BOOLEAN:Boolean:INTEGER:SYS.SQLJUTL.INT2BOOL:SYS.SQLJUTL.BOOL2INT

This changes the designated Java type from `boolean` to `Boolean`, as well as eliminating any other existing default type map entries. The rest of the conversion remains valid.

Example: Using the Type Map to Avoid Regeneration The following example uses the JPublisher type map to avoid the mapping of regenerated Java classes. Assume the following type declarations, noting that the `CITY` type is an attribute of the `TRIP` type:

    SQL> CREATE TYPE city AS OBJECT (name VARCHAR2(20), state VARCHAR2(10));
    /
    SQL> CREATE OR REPLACE TYPE trip AS OBJECT (leave DATE, place city);
    /

Now assume that you invoke JPublisher as follows:

    % jpub -u scott/tiger -s TRIP:Trip

The JPublisher output is:

    SCOTT.TRIP
    SCOTT.CITY

Only `TRIP` is specified for processing. However, the command produces the source files `City.java`, `CityRef.java`, `Trip.java`, and `TripRef.java`, because `CITY` is an attribute.

If you want to regenerate the classes for `TRIP` without regenerating the classes for `CITY`, then you can rerun JPublisher as follows:

    % jpub -u scott/tiger -addtypemap=CITY:City -s TRIP:Trip
    SCOTT.TRIP

As you can see from the output line, the `CITY` type is not reprocessed and, therefore, the `City.java` and `CityRef.java` files are not regenerated. This is because of the addition of the `CITY:City` relationship to the type map, which informs JPublisher that the existing `City` class is to be used for mapping.

### 3.1.4 JPublisher Logical Progression for Data Type Mappings

To map a given SQL or PL/SQL type to Java, JPublisher uses the following logical progression:

1. Checks the type maps to see if the mapping is already specified.

2. Checks the predefined Java mappings for SQL and PL/SQL types.

3. Checks whether the data type to be mapped is a PL/SQL `RECORD` type or an index-by table type. If it is a PL/SQL `RECORD` type, JPublisher generates a corresponding SQL object type that it can then map to Java. If it is an index-by table type, JPublisher generates a corresponding SQL collection type that it can then map to Java.

4. If none of steps 1 through 3 apply, then the data type must be a user-defined type. JPublisher generates an `ORAData` or `SQLData` class to map it according to the JPublisher option settings.

### 3.1.5 Obje ct Attribute Types

You can use a subset of the SQL data types in Table 3-1 as object attribute types. The types that can be used are listed here:

- `CHAR`, `VARCHAR`, `VARCHAR2`, `CHARACTER`

- `NCHAR`, `NVARCHAR2`

- `DATE`

- `DECIMAL`, `DEC`, `NUMBER`, `NUMERIC`

- `DOUBLE PRECISION`, `FLOAT`

- `INTEGER`, `SMALLINT`, `INT`

- `REAL`

- `RAW`, `LONG RAW`

- `CLOB`

- `BLOB`

- `BFILE`

- `NCLOB`

- Object type, `OPAQUE` type, SQLJ object type

- Nested table, `VARRAY` type

- Object reference type

JPublisher supports the following TIMESTAMP types as object attributes:

- `TIMESTAMP`

- `TIMESTAMP WITH TIMEZONE`

- `TIMESTAMP WITH LOCAL TIMEZONE`

Note: The Oracle JDBC implementation does not support the TIMESTAMP types.

### 3.1.6 REF CURSOR Types and Result Sets Mapping

If a PL/SQL stored procedure or function or a SQL query returns a `REF CURSOR`, then JPublisher generates a method, by default, to map the `REF CURSOR` to `java.sql.ResultSet`.

In addition, for a SQL query, but not for a `REF CURSOR` returned by a stored procedure or function, JPublisher generates a method to map the `REF CURSOR` to an array of rows. In this array, each row is represented by a JavaBean instance.

In addition, with a setting of `-style=``webservices-common`, if the following classes are available in the classpath, then JPublisher generates methods to map the `REF CURSOR` to the following types:

- `javax.xml.transform.Source`

- `oracle.jdbc.rowset.OracleWebRowSet`

- `org.w3c.dom.Document`

Notes: The dependency of having the class in the classpath in order to generate the mapping is specified by a `CONDITION` statement in the style file. The `CONDITION` statement lists required classes. The `webservices9` and `webservices10` style files include `webservices-common`, but override these mappings. Therefore, JPublisher will not produce these mappings with a setting of `-style=webservices9` or `-style=webservices10`.

If required, you need to perform the following actions to ensure that JPublisher can find the classes:

1. Ensure that the libraries `translator.jar`, `runtime12.jar`, and `classes12.jar` or `ojdbc14.jar` are in the classpath. These files contain JPublisher and SQLJ translator classes, SQLJ run time classes, and JDBC classes, respectively.

2. Use Java Development Kit (JDK) 1.4, for mapping to `Source`. This class is not defined in earlier JDK versions.

3. Add `ORACLE_HOME``/jdbc/lib/rowset-jsr114.jar` to the classpath, for mapping to `OracleWebRowSet`.

4. Add `ORACLE_HOME``/lib/xmlparsev2.jar` to the classpath, for mapping to `Document`.

Consider the following PL/SQL stored procedure:

    TYPE curtype1 IS REF CURSOR RETURN emp%rowtype;
    FUNCTION get1 RETURN curtype1;

If the `OracleWebRowSet` class is found in the classpath during publishing, but `Document` and `Source` are not, then JPublisher generates the following methods for the `get1` function:

    public oracle.jdbc.rowset.OracleWebRowSet get1WebRowSet()
                                     throws java.sql.SQLException;
    public java.sql.ResultSet get1() throws java.sql.SQLException;

The names of methods returning `Document` and `Source` would be `get1XMLDocument()` and `get1XMLSource()`, respectively.

Disabling Mapping to Source, OracleWebRowSet, or Document

There is currently no JPublisher option to explicitly enable or disable mapping to `Source`, `OracleWebRowSet`, or `Document`. The only condition in the `webservices-common` style file is whether the classes exist in the classpath. However, you can copy and edit your own style file if you want more control over how JPublisher maps `REF CURSOR`. The following code is an excerpt from the `webservices-common` file that has been copied and edited as an example. Descriptions of the edits follow the code.

    BEGIN_TRANSFORMATION
    MAPPING
    SOURCETYPE java.sql.ResultSet
    TARGETTYPE java.sql.ResultSet
    RETURN
    %2 = %1;
    END_RETURN;
    END_MAPPING

    MAPPING
    #CONDITION oracle.jdbc.rowset.OracleWebRowSet
    SOURCETYPE java.sql.ResultSet
    TARGETTYPE oracle.jdbc.rowset.OracleWebRowSet
    TARGETSUFFIX WebRowSet
    RETURN
    %2 = null;
    if (%1!=null)
    {
      %2 = new oracle.jdbc.rowset.OracleWebRowSet();
      %2.populate(%1);
    }
    END_RETURN
    END_MAPPING

    #MAPPING
    #CONDITION org.w3c.dom.Document oracle.xml.sql.query.OracleXMLQuery
    #SOURCETYPE java.sql.ResultSet
    #TARGETTYPE org.w3c.dom.Document
    #TARGETSUFFIX XMLDocument
    #RETURN
    #%2 = null;
    #if (%1!=null)
    #  %2= (new oracle.xml.sql.query.OracleXMLQuery
    #                                 (_getConnection(), %1)).getXMLDOM();
    #END_RETURN
    #END_MAPPING

    MAPPING
    CONDITION org.w3c.dom.Document oracle.xml.sql.query.OracleXMLQuery
              javax.xml.transform.Source javax.xml.transform.dom.DOMSource
    SOURCETYPE java.sql.ResultSet
    TARGETTYPE javax.xml.transform.Source
    TARGETSUFFIX XMLSource
    RETURN
    %2 = null;
    if (%1!=null)
      %2= new javax.xml.transform.dom.DOMSource
          ((new oracle.xml.sql.query.OracleXMLQuery
           (new oracle.xml.sql.dataset.OracleXMLDataSetExtJdbc(_getConnection(),
           (oracle.jdbc.OracleResultSet) %1))).getXMLDOM());
    END_RETURN
    END_MAPPING
    END_TRANSFORMATION

Assume that you copy this file into `myrefcursormaps.properties`. There are four `MAPPING` sections intended for mapping `REF CURSOR` to `ResultSet`, `OracleWebRowSet`, `Document`, and `Source` according to the `SOURCETYPE` and `TARGETTYPE` entries. For this example, lines are commented out using the "#" character to accomplish the following:

- The `CONDITION` statement is commented out for the `OracleWebRowSet` mapping. Because of this, JPublisher will generate a method for this mapping regardless of whether `OracleWebRowSet` is in the classpath.

- The entire `MAPPING` section is commented out for the `Document` mapping. JPublisher will not generate a method for this mapping.

Run JPublisher with the following options to use your custom mappings:

    % jpub -u scott/tiger -style=myrefcursormaps -s MYTYPE:MyType

### 3.1.7 Connection in JDBC Mapping

With the `-usertypes=jdbc` setting, JPublisher generates `SQLData` for a SQL object type. The underlying JDBC connection for a `SQLData` instance is not automatically set by the JDBC driver. Therefore, before accessing attributes in a `SQLData` instance, you must set a JDBC connection using the `setConnectionContext()` method.

Consider `Address` is a `SQLData` class generated by JPublisher with `-usertypes=jdbc`. The following code segment accesses the attribute of an `Address` instance. Note that the `setConnectionContext` call explicitly initializes the underlying JDBC connection.

    ...
    ResultSet rset = stmt.executeQuery();
    Address address = (Address) rset.getObject(1);
    address.setConnectionContext(new sqlj.runtime.ref.DefaultContext(connection));
    String addr = address.getAddress();
    ...

Note: Other `-usertypes` settings do not require setting the connection, as described in the preceding code example.

On the other hand, for `ORAData` types that JPublisher generates with the `-usertypes=oracle` setting or by default, connection initialization is not required. The underlying JDBC connection for `ORAData` is already assigned at the time it is read from `ResultSet`.

## 3.2 Sup port for PL/SQL Data Types

There are three scenarios if JPublisher encounters a PL/SQL stored procedure or function, including method of a SQL object type, which uses a PL/SQL type that is unsupported by JDBC:

- If you specify a mapping for the PL/SQL type in the default type map or user type map, then JPublisher uses that mapping.

  See Also: "JPublisher User Type Map and Default Type Map"

- If there is no mapping in the type maps, and the PL/SQL type is a `RECORD` type or an index-by table type, then JPublisher generates a corresponding SQL type that JDBC supports. For a PL/SQL `RECORD` type, JPublisher generates a SQL object type to bridge between the `RECORD` type and Java. For an index-by table type, JPublisher generates a SQL collection type for the bridge.

- If neither of the first two scenarios applies, then JPublisher issues a warning message and uses `<unsupported type>` in the generated code to represent the unsupported PL/SQL type.

The following sections discuss further details of JPublisher type mapping features for PL/SQL types unsupported by JDBC:

- Type Mapping Support for OPAQUE Types

- Type Mapping Support for Scalar Index-by Tables

- Type Mapping Support Through PL/SQL Conversion Functions

- Type Mapping Support for PL/SQL RECORD and Index-By Table Types

- Direct Use of PL/SQL Conversion Functions Versus Use of Wrapper Functions

- Other Alternatives for Data Types Unsupported by JDBC

### 3.2.1 Type Mapping Support for OPAQUE Types

This section describes JPublisher type mapping support for SQL `OPAQUE` types in general and the `SYS.XMLTYPE` SQL `OPAQUE` type in particular. It covers the following topics:

- Support for OPAQUE Types

- Support for XMLTYPE

Note: If you want JPublisher to generate wrapper classes for SQL `OPAQUE` types, then you must use an Oracle9i Database release 2 (9.2) or later installation and JDBC driver.

#### 3.2.1.1 Support for OPAQUE Types

The Oracle JDBC and SQLJ implementations support SQL `OPAQUE` types published as Java classes implementing the `oracle.sql.ORAData` interface. Such classes must contain the following public, static fields and methods:

    public static String _SQL_NAME = "SQL_name_of_OPAQUE_type";
    public static int _SQL_TYPECODE = OracleTypes.OPAQUE;
    public static ORADataFactory getORADataFactory() { ... }

If you have a Java wrapper class to map to a SQL `OPAQUE` type, and the class meets this requirement, then you can specify the mapping through the JPublisher user type map. Use the `-addtypemap` option with the following syntax to append the mapping to the user type map:

    -addtypemap=sql_opaque_type:java_wrapper_class

In Oracle Database 10g, the `SYS.XMLTYPE` SQL `OPAQUE` type is mapped to the `oracle.xdb.XMLType` Java class through the JPublisher default type map. You could accomplish the same thing explicitly through the user type map, as follows:

    -addtypemap=SYS.XMLTYPE:oracle.xdb.XMLType

Whenever JPublisher encounters a SQL `OPAQUE` type for which no type correspondence has been provided, it publishes a Java wrapper class. Consider the following SQL type defined in the `SCOTT` schema:

    CREATE TYPE X_TYP AS OBJECT (xml SYS.XMLTYPE);

The following command publishes `X_TYP` as a Java class `XTyp`:

    % jpub -u scott/tiger -s X_TYP:XTyp

By default, the `xml` attribute is published using `oracle.xdb.XMLType`, which is the predefined type mapping for `SYS.XMLTYPE`. If you clear the JPublisher default type map, then a wrapper class, `Xmltype`, will automatically be generated for the `SYS.XMLTYPE` attribute. You can verify this by invoking JPublisher as follows:

    % jpub -u scott/tiger -s X_TYP:XTyp -defaulttypemap=

The `-defaulttypemap` option is for setting the JPublisher default type map. Giving it no value, as in the preceding example, clears it.

See Also: "JPublisher User Type Map and Default Type Map" and "Type Map Options"

#### 3.2.1.2 Sup port for XMLTYPE

In Oracle Database 10g, the `SYS.XMLTYPE` SQL `OPAQUE` type is supported with the `oracle.xdb.XMLType` Java class located in `ORACLE_HOME``/lib/xsu12.jar`. This class is the default mapping, but it requires the Oracle Database 10g JDBC Oracle Call Interface (OCI) driver. It is currently not supported by the JDBC Thin driver.

The SQLJ run time provides the Java class `oracle.sql.SimpleXMLType` as an alternative mapping for `SYS.XMLTYPE`. This works on both the OCI driver and the Thin driver. With the following setting, JPublisher maps `SYS.XMLTYPE` to `oracle.sql.SimpleXMLType`:

    -adddefaulttypemap=SYS.XMLTYPE:oracle.sql.SimpleXMLType

`SimpleXMLType`, defined in `runtime12.jar`, can read an `XMLTYPE` instance as a `java.lang.String` instance, or create an `XMLTYPE` instance from a `String` instance.

For Java-to-Java type transformations, which is often necessary for Web services, the `webservices-common.properties` style file specifies the preceding mapping as well as the Java-to-Java mapping of `SimpleXMLType` to `java.lang.String`. With a setting of `-style=webservices-common`, JPublisher maps `SYS.XMLTYPE` to `SimpleXMLType` in the generated base Java class and to `String` in the user subclass.

See Also: "JPublisher-Generated Subclasses for Java-to-Java Type Transformations" and "JPublisher Styles and Style Files"

The `webservices9.properties` and `webservices10.properties` style files include the `webservices-common.properties` file. However, these files override the Java-to-Java mapping from `SimpleXMLType` to `String`. The `webservices9.properties` file maps `SimpleXMLType` to `org.w3c.dom.DocumentFragment` for the user subclass and the `webservices10.properties` file maps it to `javax.xml.transform.Source`.

Consider a setting of `-style=webservices9` as an example. The user subclass converts from `SimpleXMLType` to `DocumentFragment` or from `DocumentFragment` to `SimpleXMLType`, so that a SQL or PL/SQL method using `SYS.XMLTYPE` can be exposed as a Java method by using `org.w3c.dom.DocumentFragment`. The following example contains the JPublisher command line and portions of the PL/SQL procedure, the Java interface, the base Java class, and the user subclass.

The JPublisher command on the command line is:

    % jpub -u scott/tiger -sql=xtest:XTestBase:XTestUser#XTest -style=webservices9

The SQL definitions are:

    PROCEDUR setXMLMessage(x XMLTYPE, y NUMBER);
    FUNCTION getXMLMessage(id NUMBER) RETURN XMLTYPE;

The definitions in the `XTest` interface are:

    public org.w3c.dom.DocumentFragment getxmlmessage(java.math.BigDecimal id)
    public void setxmlmessage(org.w3c.dom.DocumentFragment x,
                              java.math.BigDecimal y)

The definitions in `XTestBase.java` are:

    public oracle.sql.SimpleXMLType _getxmlmessage (java.math.BigDecimal id)
    public void _setxmlmessage (oracle.sql.SimpleXMLType x, java.math.BigDecimal y)

The definitions in `XTestUser.java` are:

    public org.w3c.dom.DocumentFragment getxmlmessage(java.math.BigDecimal id)
    public void setxmlmessage(org.w3c.dom.DocumentFragment x,
                              java.math.BigDecimal y)

### 3.2.2 Type Mapping Sup port for Scalar Index-by Tables

The term scalar PL/SQL index-by table refers to a PL/SQL index-by table with elements of `VARCHAR` and numerical types. Starting 10g Release 2 (10.2), JPublisher can map a simple PL/SQL index-by table into a Java array, as an alternative to mapping PL/SQL index-by tables into custom JDBC types. The new option `plsqlindextable` specifies how a simple PL/SQL index-by table is mapped.

    -plsqlindextable=custom|array|int

If `-plsqlindextable=custom` is set, all indexby tables are mapped to custom JDBC types, such as `SQLData`, `CustomDatum`, or `ORAData`. If `-plsqlindextable=array` or `-plsqlindextable=``int` is set, a simple index-by table will be mapped to a Java array. With `-plsqlindextable=``int`, the `int` value specifies the array capacity, which is 32768 by default. The default setting for this option is `custom`.

Consider the following PL/SQL package:

    CREATE OR REPLACE PACKAGE indexbytable_package AS
      TYPE index_tbl1 IS TABLE OF VARCHAR2(111) INDEX BY binary_integer;
      TYPE index_tbl2 IS TABLE OF NUMBER INDEX BY binary_integer;
      TYPE varray_tbl3 IS VARRAY(100) OF VARCHAR2(20);
      TYPE nested_tbl4 IS TABLE OF VARCHAR2(20);
      FUNCTION echo_index_tbl1(a index_tbl1) RETURN index_tbl1;
      FUNCTION echo_index_tbl2(a index_tbl2) RETURN index_tbl2;
      FUNCTION echo_varray_tbl3(a varray_tbl3) RETURN varray_tbl3;
      FUNCTION echo_nested_tbl4(a nested_tbl4) RETURN nested_tbl4;
    END;
    /

Run the following command:

    % jpub -u scott/tiger -sql=indexbytable_package:IndexbyTablePackage#IndexbyTableIntf -plsqlindextable=32

The `-plsqlindextable=32` setting specifies that simple index-by tables are mapped to Java arrays, with a capacity of 32. The following interface is generated in `IndexbyTableIntf.java`:

    public interface IndexbyTableIntf
    {
     public String[] echoIndexTbl1(String[] a);
     public java.math.BigDecimal[] echoIndexTbl2(java.math.BigDecimal[] a);
     public IndexbytableintfVarrayTbl3 echoVarrayTbl4(IndexbytableintfVarrayTbl3 a);
     public IndexbytableintfNestedTbl4 echoVarrayTbl4(IndexbytableintfNestedTbl4 a);
    }

In the generated code, the simple index-by table types, `index_tbl1` and `index_tb2`, are mapped to `String[]` and `BigDecimal[]` respectively. The nested table and varray table, however, are still mapped to custom JDBC types, because they are not index-by tables and their mappings are not affected by the `-plsqlindextable` setting.

The limitation of mapping PL/SQL index-by table to an array is that the table must be indexed by integer. If a PL/SQL package contains both tables indexed by integer and by `VARCHAR`, you cannot use the setting `-plsqlindexbytable=array` or `-plsqlindexbytable=``int`. Otherwise the mapping for the table indexed by `VARCHAR` will encounter run-time errors. Instead, one must use `-plsqlindexbytable=custom`.

Mapping of the index-by table elements follows the JDBC type mappings. For example, with JDBC mapping, `SMALLINT` is mapped to the Java `int` type. Therefore, an index-by table of `SMALLINT` is mapped to `int[]`. The `-plsqlindexbytable=array` or `-plsqlindexbytable=``int` setting will be ignored if Oracle mappings are turned on for numbers, that is, `-numbertypes=oracle`. The reason is that the Java array mapped to the index-by table must have string or numerical Java types as elements, while Oracle mappings map SQL numbers into `oracle.sql` types.

The Oracle JDBC drivers directly support PL/SQL scalar index-by tables with numeric or character elements. An index-by table with numeric elements can be mapped to the following Java array types:

- `int[]`

- `double[]`

- `float[]`

- `java.math.BigDecimal[]`

- `oracle.sql.NUMBER[]`

An index-by table with character elements can be mapped to the following Java array types:

- `String[]`

- `oracle.sql.CHAR[]`

In the following circumstances, you must convey certain information for an index-by table type, as described:

- Whenever you use the index-by table type in an `OUT` or `IN OUT` parameter, you must specify the maximum number of elements, which is otherwise optional. You can specify the maximum number of elements using the customary syntax for Java array allocation. For example, you could specify `int[100]` to denote a type that can accommodate up to 100 elements or `oracle.sql.CHAR[20]` for up to 20 elements.

- For index-by tables with character elements, you can optionally specify the maximum size of an individual element, in bytes. This setting is defined using the SQL-like size syntax. For example, for an index-by table used for `IN` arguments, you could specify `String[](30)`. You could also specify `oracle.sql.CHAR[20](255)` for an index-by table of maximum length 20, the elements of which will not exceed 255 bytes each.

Use the JPublisher `-addtypemap` option to add instructions to the user type map to specify correspondences between PL/SQL types, which are scalar index-by tables, and the corresponding Java array types. The size hints that are given using the syntax outlined earlier are embedded into the generated SQLJ class and thus conveyed to JDBC at run time.

As an example, consider the following code fragment from the definition of the `INDEXBY` PL/SQL package in the `SCOTT` schema. Assume this is available in a file called `indexby.sql`.

    CREATE OR REPLACE PACKAGE indexby AS

    --  jpub.addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
    --  jpub.addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
    --  jpub.addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

     TYPE varchar_ary IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
     TYPE integer_ary IS TABLE OF INTEGER        INDEX BY BINARY_INTEGER;
     TYPE float_ary   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;

     FUNCTION get_float_ary RETURN float_ary;
     PROCEDURE pow_integer_ary(x integer_ary, y OUT integer_ary);
     PROCEDURE xform_varchar_ary(x IN OUT varchar_ary);

    END indexby;
    /
    CREATE OR REPLACE PACKAGE BODY indexby IS ...
    /

The following are the required `-addtypemap` directives for mapping the three index-by table types:

    -addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
    -addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
    -addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

Note that depending on the operating system shell you are using, you may have to quote options that contain square brackets `[`...`]` or parentheses `(`...`)`. You can avoid this by placing such options into a JPublisher properties file, as follows:

    jpub.addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
    jpub.addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
    jpub.addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

See Also: "Properties File Structure and Syntax" and "Additional Entry to the User Type Map"

Additionally, as a feature of convenience, JPublisher directives in a properties file are recognized when placed behind a "`--`" prefix (two dashes), whereas any entry that does not start with `"jpub."` or with `"-- jpub.`" is ignored. So, you can place JPublisher directives into SQL scripts and reuse the same SQL scripts as JPublisher properties files. Thus, after invoking the `indexby.sql` script to define the `INDEXBY` package, you can now run JPublisher to publish this package as a Java class, `IndexBy`, as follows:

    % jpub -u scott/tiger -s INDEXBY:IndexBy -props=indexby.sql

As mentioned previously, you can use this mapping of scalar index-by tables only with the Oracle JDBC drivers. If you are using another driver or if you want to create driver-independent code, then you must define SQL types that correspond to the index-by table types, as well as defining conversion functions that map between the two.

See Also: "Type Mapping Support for PL/SQL RECORD and Index-By Table Types"

### 3.2.3 Type Map ping Support Through PL/SQL Conversion Functions

This section discusses the mechanism that JPublisher uses for supporting PL/SQL types in Java code, through PL/SQL conversion functions that convert between each PL/SQL type and a corresponding SQL type to allow access by JDBC.

In general, Java programs do not support the binding of PL/SQL-specific types. The only way you can use such types from Java is to use PL/SQL code to map them to SQL types, and then access these SQL types from Java. However, one exception is the scalar index-by table type.

JPublisher makes this task more convenient through the use of its type maps. For a particular PL/SQL type, specify the following information in a JPublisher type map entry:

- Name of the PL/SQL type, typically of the form:

      SCHEMA.PACKAGE.TYPE

- Name of the corresponding Java wrapper class

- Name of the SQL type that corresponds to the PL/SQL type

  You must be able to directly map this type to the Java wrapper type. For example, if the SQL type is `NUMBER`, then the corresponding Java type could be `int`, `double`, `Integer`, `Double`, `java.math.BigDecimal`, or `oracle.sql.NUMBER`. If the SQL type is an object type, then the corresponding Java type would be an object wrapper class that implements the `oracle.sql.ORAData` or `java.sql.SQLData` interface. The object wrapper class is typically generated by JPublisher.

- Name of a PL/SQL conversion function that maps the SQL type to the PL/SQL type

- Name of a PL/SQL conversion function that maps the PL/SQL type to the SQL type

The `-addtypemap` specification for this has the following form:

    -addtypemap=plsql_type:java_type:sql_type:sql_to_plsql_fun:plsql_to_sql_fun

See Also: "Type Map Options"

As an example, consider a type map entry for supporting the PL/SQL `BOOLEAN` type. It consists of the following specifications:

- Name of the PL/SQL type: `BOOLEAN`

- Specification to map it to Java `boolean`

- Corresponding SQL type: `INTEGER`

  JDBC considers `boolean` values as special numeric values.

- Name of the PL/SQL function that maps from SQL to PL/SQL: `INT2BOOL`

  The code for the function is:

      FUNCTION int2bool(i INTEGER) RETURN BOOLEAN IS
      BEGIN IF i IS NULL THEN RETURN NULL;
            ELSE RETURN i<>0;
            END IF;
      END int2bool;

- Name of the PL/SQL function that maps from PL/SQL to SQL: `BOOL2INT`

  The code for the function is:

      FUNCTION bool2int(b BOOLEAN) RETURN INTEGER IS
      BEGIN IF b IS NULL THEN RETURN NULL;
            ELSIF b THEN RETURN 1;
            ELSE RETURN 0;
            END IF;
      END bool2int;

You can put all this together in the following type map entry:

    -addtypemap=BOOLEAN:boolean:INTEGER:INT2BOOL:BOOL2INT

Such a type map entry assumes that the SQL type, the Java type, and both conversion functions have been defined in SQL, Java, and PL/SQL, respectively. Note that there is already an entry for PL/SQL `BOOLEAN` in the JPublisher default type map. If you want to try the preceding type map entry, you will have to override the default type map. You can use the JPublisher `-defaulttypemap` option to accomplish this, as follows:

    % jpub -u scott/tiger -s SYS.SQLJUTL:SQLJUtl
           -defaulttypemap=BOOLEAN:boolean:INTEGER:INT2BOOL:BOOL2INT

Notes: In some cases, such as with `INT2BOOL` and `BOOL2INT` in the preceding example, JPublisher has conversion functions that are predefined, typically in the `SYS.SQLJUTL` package. In other cases, such as for `RECORD` types and index-by table types, JPublisher generates conversion functions during execution. Although this manual describes conversions as mapping between SQL and PL/SQL types, there is no intrinsic restriction to PL/SQL in this approach. You could also map between different SQL types. In fact, this is done in the JPublisher default type map to support SQL `INTERVAL` types, which are mapped to `VARCHAR2` values and back.

Be aware that under some circumstances, PL/SQL wrapper functions are also created by JPublisher. Each wrapper function wraps a stored procedure that uses PL/SQL types. It calls this original stored procedure and processes its PL/SQL input or output through the appropriate conversion functions so that only the corresponding SQL types are exposed to Java. The following JPublisher options control how JPublisher creates code for invocation of PL/SQL stored procedures that use PL/SQL types, including the use of conversion functions and possibly the use of wrapper functions:

- `-plsqlpackage=``plsql_package`

  This option determines the name of the PL/SQL package into which JPublisher generates the PL/SQL conversion functions: a function to convert each unsupported PL/SQL type to the corresponding SQL type and a function to convert from each corresponding SQL type back to the PL/SQL type. Optionally, depending on how you set the `-plsqlmap` option, the package also contains wrapper functions for the original stored procedures, with each wrapper function invoking the appropriate conversion function.

  If you do not specify a package name, then JPublisher uses `JPUB_PLSQL_WRAPPER`.

- `-plsqlfile=``plsql_wrapper_script``,``plsql_dropper_script`

  This option determines the name of the wrapper script and dropper script that JPublisher creates. The wrapper script creates necessary SQL types that map to unsupported PL/SQL types and also creates the PL/SQL package. The dropper script drops these SQL types and the PL/SQL package.

  If the files already exist, then they will be overwritten. If you do not specify any file names, then JPublisher will write to the files named `plsql_wrapper.sql` and `plsql_dropper.sql`.

- `-plsqlmap=``flag`

  This option specifies whether JPublisher generates wrapper functions for stored procedures that use PL/SQL types. Each wrapper function calls the corresponding stored procedure and the appropriate PL/SQL conversion functions for PL/SQL input or output of the stored procedure. Only the corresponding SQL types are exposed to Java. The `flag` setting can be any of the following:

  - `true`

    This is the default setting. JPublisher generates PL/SQL wrapper functions only as needed. For any given stored procedure, if the Java code to call it and convert its PL/SQL types directly is simple enough, and if PL/SQL types are used only as `IN` parameters or for the function return, then the generated code calls the stored procedure directly instead. The code then processes the PL/SQL input or output through the appropriate conversion functions.

    If a PL/SQL type is used as an `OUT` or `IN OUT` parameter, then wrapper functions are required, because conversions between PL/SQL and SQL representations may be necessary either before or after calling the original stored procedure.

  - `false`

    JPublisher does not generate PL/SQL wrapper functions. If it encounters a PL/SQL type in a signature that cannot be supported by a direct call and conversion, then it skips the generation of Java code for the particular stored procedure.

  - `always`

    JPublisher generates a PL/SQL wrapper function for every stored procedure that uses a PL/SQL type. This setting is useful for generating a proxy PL/SQL package that complements an original PL/SQL package, providing JDBC-accessible signatures for those functions or procedures that were not accessible through JDBC in the original package.

See Also: "Direct Use of PL/SQL Conversion Functions Versus Use of Wrapper Functions" and "PL/SQL Code Generation Options"

### 3.2.4 Type Mapping Sup port for PL/SQL RECORD and Index-By Table Types

JPublisher automatically publishes a PL/SQL `RECORD` type whenever it publishes a PL/SQL stored procedure or function that uses that type as an argument or return type. The same is true for PL/SQL index-by table types. This is the only way that a `RECORD` type or index-by table type can be published. There is no way to explicitly request any such types to be published through JPublisher option settings.

Note: The following are limitations to the JPublisher support for PL/SQL `RECORD` and index-by table types: An intermediate wrapper layer is required to map a `RECORD` or index-by-table argument to a SQL type that JDBC can support. In addition, JPublisher cannot fully support the semantics of index-by tables. An index-by table is similar in structure to a Java hashtable, but information is lost when JPublisher maps this to a SQL `TABLE` type. If you use the JDBC OCI driver and require only the publishing of scalar index-by tables, then you can use the direct mapping between Java and these types.

The following sections demonstrate JPublisher support for PL/SQL `RECORD` types and index-by table types:

- Sample Package for RECORD Type and Index-By Table Type Support

- Support for RECORD Types

- Support for Index-By Table Types

#### 3.2.4.1 Sample Package for RECORD Type and Index-By Table Type Support

The following PL/SQL package is used to illustrate JPublisher support for PL/SQL `RECORD` and index-by table types:

    CREATE OR REPLACE PACKAGE company IS
      TYPE emp_rec IS RECORD (empno NUMBER, ename VARCHAR2(10));
      TYPE emp_tbl IS TABLE OF emp_rec INDEX BY binary_integer;
      PROCEDURE set_emp_rec(er emp_rec);
      FUNCTION get_emp_rec(empno number) RETURN emp_rec;
      FUNCTION get_emp_tbl RETURN emp_tbl;
    END;

This package defines a PL/SQL `RECORD` type, `EMP_REC`, and a PL/SQL index-by table type, `EMP_TBL`. Use the following command to publish the `COMPANY` package:

    % jpub -u scott/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
      -plsqlfile=wrapper1.sql,dropper1.sql

The JPublisher output is:

    SCOTT.COMPANY
    SCOTT."COMPANY.EMP_REC"
    SCOTT."COMPANY.EMP_TBL"
    J2T-138, NOTE: Wrote PL/SQL package WRAPPER1 to file wrapper1.sql.
    Wrote the dropping script to file dropper1.sql

In this example, JPublisher generates `Company.java` for the Java wrapper class for the `COMPANY` package, as well as the following SQL and Java entities:

- The `wrapper1.sql` script that creates the SQL types corresponding to the PL/SQL `RECORD` and index-by table types, and also creates the conversion functions between the SQL types and the PL/SQL types

- The `dropper1.sql` script that removes the SQL types and conversion functions created by `wrapper1.sql`

- The `CompanyEmpRec.java` source file for the Java wrapper class for the SQL object type that is generated for the PL/SQL `RECORD` type

- The `CompanyEmpTbl.java` source file for the Java wrapper class for the SQL collection type that is generated for the PL/SQL index-by table type

#### 3.2.4.2 Support for RECORD Types

This section continues the example from Sample Package for RECORD Type and Index-By Table Type Support. For the PL/SQL `RECORD` type, `EMP_REC`, JPublisher generates the corresponding `COMPANY_EMP_REC` SQL object type. JPublisher also generates the conversion functions between the two. In this example, the following is generated in `wrapper1.sql` for `EMP_REC`:

    CREATE OR REPLACE TYPE COMPANY_EMP_REC AS OBJECT (
                           EMPNO NUMBER(22),
                           ENAME VARCHAR2(10)
    );
    /
    -- Declare package containing conversion functions between SQL and PL/SQL types
    CREATE OR REPLACE PACKAGE WRAPPER1 AS
       -- Declare the conversion functions the PL/SQL type COMPANY.EMP_REC
            FUNCTION PL2COMPANY_EMP_REC(aPlsqlItem COMPANY.EMP_REC)
            RETURN COMPANY_EMP_REC;
            FUNCTION COMPANY_EMP_REC2PL(aSqlItem COMPANY_EMP_REC)
            RETURN COMPANY.EMP_REC;
    END WRAPPER1;
    /

In addition, JPublisher publishes the `COMPANY_EMP_REC` SQL object type into the `CompanyEmpRec.java` Java source file.

Once the PL/SQL `RECORD` type is published, you can add the mapping to the type map. The following is an entry in a sample JPublisher properties file, `done.properties`:

    jpub.addtypemap=SCOTT.COMPANY.EMP_REC:CompanyEmpRec:COMPANY_EMP_REC:
    WRAPPER1.COMPANY_EMP_REC2PL:WRAPPER1.PL2COMPANY_EMP_REC

Use this type map entry whenever you publish a package or type that refers to the `RECORD` type, `EMP_REC`. For example, the following JPublisher invocation uses `done.properties` with this type map entry:

    % jpub -u scott/tiger -p done.properties -s COMPANY -plsqlpackage=WRAPPER2
           -plsqlfile=wrapper2.sql,dropper2.sql

The JPublisher output is:

    SCOTT.COMPANY
    SCOTT."COMPANY.EMP_TBL"
    J2T-138, NOTE: Wrote PL/SQL package WRAPPER2 to file wrapper2.sql.
    Wrote the dropping script to file dropper2.sql

#### 3.2.4.3 Support for Index-By Table Types

This section continues the example from Sample Package for RECORD Type and Index-By Table Type Support.

To support an index-by table type, a SQL collection type must be defined that permits conversion to and from the PL/SQL index-by table type. JPublisher also supports PL/SQL nested tables and VARRAYs in the same fashion. Therefore, JPublisher generates the same code for the following three definitions of `EMP_TBL`:

    TYPE emp_tbl IS TABLE OF emp_rec INDEX BY binary_integer;
    TYPE emp_tbl IS TABLE OF emp_rec;
    TYPE emp_tbl IS VARRAY OF emp_rec;

For the PL/SQL index-by table type `EMP_TBL`, JPublisher generates a SQL collection type, and conversion functions between the index-by table type and the SQL collection type.

In addition to what was shown for the `RECORD` type earlier, JPublisher generates the following:

    -- Declare the SQL type for the PL/SQL type COMPANY.EMP_TBL
    CREATE OR REPLACE TYPE COMPANY_EMP_TBL AS TABLE OF COMPANY_EMP_REC;
    /
    -- Declare package containing conversion functions between SQL and PL/SQL types
    CREATE OR REPLACE PACKAGE WRAPPER1 AS
       -- Declare the conversion functions for the PL/SQL type COMPANY.EMP_TBL
            FUNCTION PL2COMPANY_EMP_TBL(aPlsqlItem COMPANY.EMP_TBL)
            RETURN COMPANY_EMP_TBL;
            FUNCTION COMPANY_EMP_TBL2PL(aSqlItem COMPANY_EMP_TBL)
            RETURN COMPANY.EMP_TBL;
    ...
    END WRAPPER1;

JPublisher further publishes the SQL collection type into `CompanyEmpTbl.java`.

As with a PL/SQL `RECORD` type, once a PL/SQL index-by table type is published, the published result, including the Java wrapper classes, the SQL collection type, and the conversion functions, can be used in the future for publishing PL/SQL packages involving that PL/SQL index-by table type. For example, if you add the following entry into a properties file that you use in invoking JPublisher, say `done.properties`, then JPublisher will use the provided type map and avoid republishing that index-by table type:

    jpub.addtypemap=SCOTT.COMPANY.EMP_TBL:CompanyEmpTbl:COMPANY_EMP_TBL:
    WRAPPER1.COMPANY_EMP_TBL2PL:WRAPPER1.PL2COMPANY_EMP_TBL

See Also: "JPublisher User Type Map and Default Type Map"

### 3.2.5 Direct Use of PL/SQL Conve rsion Functions Versus Use of Wrapper Functions

In generating Java code to invoke a stored procedure that uses a PL/SQL type, JPublisher can use either of the following modes of operation:

- Invoke the stored procedure directly, which processes the PL/SQL input or output through the appropriate conversion functions.

- Invoke a PL/SQL wrapper function, which in turn calls the stored procedure and processes its PL/SQL input or output through the appropriate conversion functions. The wrapper function that is generated by JPublisher uses the corresponding SQL types for input or output.

The `-plsqlmap` option determines whether JPublisher uses the first mode, the second mode, or possibly either mode, depending on circumstances.

See Also: "Generation of PL/SQL Wrapper Functions"

As an example, consider the `SCOTT.COMPANY.GET_EMP_TBL` PL/SQL stored procedure that returns the `EMP_TBL` PL/SQL index-by table type. Assume that the `COMPANY` package, introduced in "Sample Package for RECORD Type and Index-By Table Type Support", is processed by JPublisher through the following command:

    % jpub -u scott/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
           -plsqlfile=wrapper1.sql,dropper1.sql -plsqlmap=false

The JPublisher output is:

    SCOTT.COMPANY
    SCOTT."COMPANY.EMP_REC"
    SCOTT."COMPANY.EMP_TBL"
    J2T-138, NOTE: Wrote PL/SQL package WRAPPER1 to file wrapper1.sql.
    Wrote the dropping script to file dropper1.sql

With this command, JPublisher creates the following:

- SQL object type `COMPANY_EMP_REC` to map to the PL/SQL `RECORD` type `EMP_REC`

- SQL collection type `COMPANY_EMP_TBL` to map to the PL/SQL index-by table type `EMP_TBL`

- Java classes to map to `COMPANY`, `COMPANY_EMP_REC`, and `COMPANY_EMP_TBL`

- PL/SQL package `WRAPPER1`, which includes the PL/SQL conversion functions to convert between the PL/SQL index-by table type and the SQL collection type

In this example, assume that the conversion function `PL2COMPANY_EMP_TBL` converts from the PL/SQL `EMP_TBL` type to the SQL `COMPANY_EMP_TBL` type. Because of the setting `-plsqlmap=false`, no wrapper functions are created. The stored procedure is called with the following JDBC statement in generated Java code:

    conn.prepareOracleCall =
    ("BEGIN :1 := WRAPPER1.PL2COMPANY_EMP_TBL(SCOTT.COMPANY.GET_EMP_TBL()) \n; END;");

`SCOTT.COMPANY.GET_EMP_TBL` is called directly, with its `EMP_TBL` output being processed through the `PL2COMPANY_EMP_TBL` conversion function to return the desired `COMPANY_EMP_TBL` SQL type.

By contrast, if you run JPublisher with the setting `-plsqlmap=always`, then `WRAPPER1` also includes a PL/SQL wrapper function for every PL/SQL stored procedure that uses a PL/SQL type. In this case, for any given stored procedure, the generated Java code calls the wrapper function instead of the stored procedure. The wrapper function, in this example `WRAPPER1.GET_EMP_TBL`, calling the original stored procedure and processing its output through the conversion function is as follows:

    FUNCTION  GET_EMP_TBL()
       BEGIN
          RETURN WRAPPER1.PL2COMPANY_EMP_TBL(SCOTT.COMPANY.GET_EMP_TBL())
       END;

In the generated Java code, the JDBC statement calling the wrapper function is:

    conn.prepareOracleCall("BEGIN :1=SCOTT.WRAPPER1.GET_EMP_TBL() \n; END;");

If `-plsqlmap=true`, then JPublisher uses direct calls to the original stored procedure wherever possible. However, in the case of any stored procedure for which the Java code for direct invocation and conversion is too complex or any stored procedure that uses PL/SQL types as `OUT` or `IN OUT` parameters, JPublisher generates a wrapper function and calls that function in the generated code.

### 3.2.6 Other Alternatives for Data Types Unsupported by JDBC

The preceding sections describe the mechanisms that JPublisher employs to access PL/SQL types unsupported by JDBC. As an alternative to using JPublisher in this way, you can try one of the following:

- Rewrite the PL/SQL method to avoid using the type

- Write an anonymous block that does the following:

  - Converts input types that JDBC supports into the input types used by the PL/SQL stored procedure

  - Converts output types used by the PL/SQL stored procedure into output types that JDBC supports

## 3.3 JPublish er Styles and Style Files

JPublisher style files allow you to specify Java-to-Java type mappings. This is to ensure that generated classes can be used in Web services. As an example, `CLOB` types, such as `java.sql.Clob` and `oracle.sql.CLOB`, cannot be used in Web services, but the data can be used if it is converted to a type that is supported by Web services, such as `java.lang.String`. JPublisher must generate user subclasses to implement its use of style files and Java-to-Java type transformations.

See Also: "JPublisher-Generated Subclasses for Java-to-Java Type Transformations"

Typically, style files are provided by Oracle, but there may be situations in which you may want to edit or create your own.

The following sections discuss features and usage of styles and style files:

- Style File Specifications and Locations

- Style File Format

- Summary of Key Java-to-Java Type Mappings in Oracle Style Files

- Use of Multiple Style Files

### 3.3.1 Style File Specifications and Locations

Use the JPublisher `-style` option to specify the base name of a style file:

    -style=stylename

Based on the `stylename` you specify, JPublisher looks for a style file as follows, and uses the first file that it finds:

1. It looks for the following resource in the classpath:

       /oracle/jpub/mesg/stylename.properties

2. It takes `stylename` as a resource name, possibly qualified, and looks for the following in the classpath:

       /stylename-dir/stylename-base.properties

3. It takes `stylename` as a name, possibly qualified, and looks for the following file in the current directory:

       stylename.properties

   In this case, `stylename` can optionally include a directory path. If you use the setting `-style=mydir/foo`, for example, then JPublisher looks for `mydir/foo.properties` relative to the current directory.

If no matching file is found, JPublisher generates an exception.

See Also: "Style File for Java-to-Java Type Mappings"

As an example of the first scenario, if the resource `/oracle/jpub/mesg/webservices.properties` exists in `ORACLE_HOME``/sqlj/lib/translator.jar` and `translator.jar` is found in the classpath, then the `-style=webservices` setting uses `/oracle/jpub/mesg/webservices.properties` from `translator.jar`, even if there is a `webservices.properties` file in the current directory.

However, if you specify `-style=mystyle` and a `mystyle.properties` resource is not found in `/oracle/jpub/mesg`, but there is a `mystyle.properties` file in the current directory, then that is used.

Note: Oracle currently provides three style files:
    /oracle/jpub/mesg/webservices-common.properties
    /oracle/jpub/mesg/webservices10.properties
    /oracle/jpub/mesg/webservices9.properties
These are in the `translator.jar` file, which must be included in your classpath. Each file maps Oracle JDBC types to Java types supported by Web services. Note that the `webservices-common.properties` file is for general use and is included by both `webservices10.properties` and `webservices9.properties`. To use Web services in Oracle Database 10g, specify the following style file:
    -style=webservices10
To use Web services in Oracle9i, specify `-style=webservices9`.

### 3.3.2 Style File Format

The key portion of a style file is the `TRANSFORMATION` section. This section comprises everything between the `TRANSFORMATION` tag and `END_TRANSFORMATION` tag. It describes the type transformations, or Java-to-Java mappings, to be applied to types used for object attributes or in method signatures.

For convenience, there is an `OPTIONS` section in which you can specify any other JPublisher option settings. Because of this section, a style file can replace the functionality of any other JPublisher properties file, in addition to specifying mappings.

This section covers the following topics:

- Style File TRANSFORMATION Section

- Style File OPTIONS Section

Note: The following details about style files are provided for general information only and are subject to change.

#### 3.3.2.1 Style File TRANSFORMATION Section

This section provides a template for a style file `TRANSFORMATION` section, with comments. Within the `TRANSFORMATION` section, there is a `MAPPING` section for each mapping that you specify. The `MAPPING` section starts at a `MAPPING` tag and ends with an `END_MAPPING` tag. Each `MAPPING` section includes a number of subtags with additional information. In the `MAPPING` section, the `SOURCETYPE` and `TARGETTYPE` tags are the required subtags. Within each `TARGETTYPE` section, you should generally provide information for at least the `RETURN`, `IN`, and `OUT` cases, using the corresponding tags. The following code illustrates the structure of a typical `TRANSFORMATION` section:

    TRANSFORMATION

     IMPORT
     # Packages to be imported by the generated classes
     END_IMPORT

     # THE FOLLOWING OPTION ONLY APPLIES TO PL/SQL PACKAGES
     # This interface should be implemented/extended by
     # the methods in the user subclasses and interfaces
     # This option takes no effect when subclass is not generated.
     SUBCLASS_INTERFACE java_interface

     # THE FOLLOWING OPTION ONLY APPLIES TO PL/SQL PACKAGES
     # Each method in the interface and the user subclass should
     # throw this exception (the default SQLException will be caught
     # and re-thrown as an exception specified here)
     # This option takes no effect when subclass is not generated.
     SUBCLASS_EXCEPTION Java_exception_type

     STATIC
     # Any code provided here is inserted at the
     # top level of the generated subclass regardless
     # of the actual types used.
     END_STATIC

     # Enumerate as many MAPPING sections as needed.

     MAPPING
     SOURCETYPE Java_source_type
     # Can be mapped to several target types.
     TARGETTYPE Java_target_type

     # With CONDITION specified, the source-to-target
     # mapping is carried out only when the listed Java
     # classes are present during publishing.
     # The CONDITION section is optional.
     CONDITION list_of_java_classes

     IN
     # Java code for performing the transformation
     # from source type argument %1 to the target
     # type, assigning it to %2.
     END_IN
     IN_AFTER_CALL
     # Java code for processing IN parameters
     # after procedure call.
     END_IN_AFTER_CALL
     OUT
     # Java code for performaing the transformation
     # from a target type instance %2 to the source
     # type, assigning it to %1.
     END_OUT
     RETURN
     # Java code for performing the transformation
     # from source type argument %1 to the target
     # type and returning the target type.
     END_RETURN

     # Include the code given by a DEFINE...END_DEFINE block
     # at the end of this template file.
     USE defined_name

     # Holder for OUT/INOUT of the type defined by SOURCETYPE.
     HOLDER Java_holder_type
     END_TARGETTYPE

     # More TARGETTYPE sections, as needed

     END_MAPPING

     DEFAULT_HOLDER
     # JPublisher will generate holders for types that do
     # not have HOLDER entries defined in this template.
     # This section includes a template for class definitions
     # from which JPublisher will generate .java files for
     # holder classes.
     END_DEFAULT_HOLDER

     # More MAPPING sections, as needed

     DEFINE defined_name
     # Any code provided here is inserted at the
     # top level of the generated class if the
     # source type is used.
     END_DEFINE
     # More DEFINE sections, as needed

    END_TRANSFORMATION

Notes: Style files use `ISO8859_1` encoding. Any characters that cannot be represented directly in this encoding must be represented in Unicode escape sequences. It is permissible to have multiple `MAPPING` sections with the same `SOURCETYPE` specification. For argument type, JPublisher uses the last of these `MAPPING` sections that it encounters.

See Also: "Passing Output Parameters in JAX-RPC Holders" for a discussion of holders

#### 3.3.2.2 Style File OPTIONS Section

For convenience, you can specify any desired JPublisher option settings in the `OPTIONS` section of a style file, in the standard format for JPublisher properties files. The syntax for the same is as follows:

    OPTIONS
     # Comments
     jpub.option1=value1
     jpub.option2=value2
     ...
    END_OPTIONS

### 3.3.3 Summary of Key Java-to-Java Type Mappings in Oracle Style Files

The Oracle style files `webservices-common.properties`, `webservices9.properties`, and `webservices10.properties`, through their `SOURCETYPE` and `TARGETTYPE` specifications, have a number of important Java-to-Java type mappings to support Web services and `REF CURSOR` mappings. These mappings are summarized in Table 3-2.

Table 3-2 Summary of Java-to-Java Type Mappings in Oracle Style Files

Source Type | Target Type
--- | ---
`oracle.sql.NString` | `java.lang.String`
`oracle.sql.CLOB` | `java.lang.String`
`oracle.sql.BLOB` | `byte[]`
`oracle.sql.BFILE` | `byte[]`
`java.sql.Timestamp` | `java.util.Date`
`java.sql.ResultSet` | `oracle.jdbc.rowset.OracleWebRowSet` `org.w3c.dom.Document` `javax.xml.transform.Source`
`oracle.sql.SimpleXMLType` | `java.lang.String` (`webservices-common`) `org.w3c.dom.DocumentFragment` (`webservices9`) `javax.xml.transform.Source` (`webservices10`)

The `webservices9` and `webservices10` files include `webservices-common` before specifying mappings specific to these files. For `SimpleXMLType`, note that `DocumentFragment` overrides `String` if you set `-style=webservices9` and `Source` overrides `String`, if you set `-style=webservices10`.

See Also: "REF CURSOR Types and Result Sets Mapping"

### 3.3.4 Use of Multiple Style Files

JPublisher allows multiple `-style` options on the command line, with the following behavior:

- The `OPTIONS` sections are concatenated.

- The `TRANSFORMATION` sections are concatenated, except that the entries in the `MAPPING` sections are overridden, as applicable. A `MAPPING` entry from a style file specified later on the command line overrides a `MAPPING` entry with the same `SOURCETYPE` specification from a style file specified earlier on the command line.

This functionality is useful if you want to overwrite type mappings defined earlier or add new type mappings. For example, if you want to map `SYS.XMLTYPE` to `java.lang.String`, then you can append the setting `-style=xml2string` to the JPublisher command line. This example assumes that the `./xml2string.properties` style file will be accessed. This style file is defined as follows:

    OPTIONS
           jpub.defaulttypemap=SYS.XMLTYPE:oracle.sql.SimpleXMLType
          END_OPTIONS
          TRANSFORM
          MAPPING
          SOURCETYPE oracle.sql.SimpleXMLType
          TARGETTYPE java.lang.String
          # SimpleXMLType => String
          OUT
          %2 = null;
          if (%1!=null) %2=%1.getstringval();
          END_OUT
          # String => SimpleXMLType
          IN
          %1 = null;
          if (%2!=null)
          {
            %1 = new %p.%c(_getConnection());
            %1 = %1.createxml(%2);
          }
          END_IN
          END_TARGETTYPE
          END_MAPPING
          END_TRANSFORM

Continuing this example, assume the following PL/SQL stored procedure definition:

    PROCEDURE foo (arg XMLTYPE);

JPublisher maps this as follows in the base class:

    void foo (arg oracle.sql.SimpleXMLType);

And JPublisher maps it as follows in the user subclass:

    void foo (arg String);

Note: By default, JPublisher maps `SYS.XMLTYPE` to `oracle.xdb.XMLType`.